import pandas as pd
import openpyxl
import os
from openpyxl.utils import get_column_letter

# 设置文件路径
source_file_path = "D:/VaultView/SOLIDWORKS数据管理/5-Giant-laser工作体系/非质量管理体系文件/业务部/瀚越已发货包装数据总表.xlsx"

def check_merge_cells():
    """检查Excel文件中的合并单元格情况"""
    try:
        # 使用openpyxl直接读取Excel文件以检查合并单元格
        wb = openpyxl.load_workbook(source_file_path, data_only=True)
        sheet = wb["已发货"]
        
        print("===== 合并单元格检查结果 =====")
        print(f"工作表: '已发货'")
        print(f"总行数: {sheet.max_row}")
        print(f"总列数: {sheet.max_column}")
        
        # 检查是否有合并单元格
        merge_ranges = sheet.merged_cells.ranges
        print(f"发现合并单元格数量: {len(merge_ranges)}")
        
        # 统计E列的合并单元格（VBA代码中特别处理的列）
        e_column_merges = []
        for merge in merge_ranges:
            if merge.min_col == 5 and merge.max_col == 5:  # E列是第5列
                e_column_merges.append(merge)
        
        print(f"E列合并单元格数量: {len(e_column_merges)}")
        
        # 显示前5个E列合并单元格的范围
        if e_column_merges:
            print("前5个E列合并单元格范围:")
            for i, merge in enumerate(e_column_merges[:5]):
                print(f"  {i+1}. 行范围: {merge.min_row}-{merge.max_row}")
        
        # 使用pandas读取数据进行对比
        print("\n===== pandas读取测试 =====")
        df = pd.read_excel(source_file_path, sheet_name="已发货")
        print(f"pandas读取的行数: {len(df)}")
        print(f"pandas读取的列数: {len(df.columns)}")
        
        # 检查VBA中特别搜索的B列
        print(f"B列数据类型: {df.iloc[:, 1].dtype}")
        
        return True
        
    except Exception as e:
        print(f"检查过程中发生错误: {str(e)}")
        return False

def search_with_merge_support(search_text):
    """模拟VBA的搜索逻辑，处理合并单元格"""
    try:
        # 使用openpyxl读取文件
        wb = openpyxl.load_workbook(source_file_path, data_only=True)
        sheet = wb["已发货"]
        
        # 获取表头
        headers = []
        for col in range(1, sheet.max_column + 1):
            headers.append(sheet.cell(row=1, column=col).value)
        
        # 记录已处理的行
        processed_rows = set()
        results = []
        
        print(f"\n===== 搜索文本: '{search_text}' =====")
        
        # 遍历所有行（跳过表头）
        for row in range(2, sheet.max_row + 1):
            # 跳过已处理的行
            if row in processed_rows:
                continue
            
            # 获取B列单元格的值
            cell_b = sheet.cell(row=row, column=2)  # B列是第2列
            cell_value = cell_b.value
            
            # 检查是否包含搜索文本
            if cell_value and isinstance(cell_value, str) and search_text.lower() in cell_value.lower():
                # 检查E列是否为合并单元格
                cell_e = sheet.cell(row=row, column=5)  # E列是第5列
                
                # 查找该单元格所属的合并区域
                merge_range = None
                for merge in sheet.merged_cells.ranges:
                    if cell_e.coordinate in merge:
                        merge_range = merge
                        break
                
                # 确定要复制的行范围
                if merge_range:
                    start_row = merge_range.min_row
                    end_row = merge_range.max_row
                else:
                    start_row = row
                    end_row = row
                
                # 添加到结果
                for r in range(start_row, end_row + 1):
                    if r not in processed_rows:
                        row_data = []
                        for col in range(1, sheet.max_column + 1):
                            row_data.append(sheet.cell(row=r, column=col).value)
                        results.append(row_data)
                        processed_rows.add(r)
        
        print(f"找到匹配记录数: {len(results)}")
        
        # 如果有结果，保存到测试文件
        if results:
            # 创建包含结果的DataFrame
            result_df = pd.DataFrame(results, columns=headers)
            
            # 添加搜索文本标识
            result_df.insert(0, "搜索文本", search_text)
            
            # 保存结果
            test_output_path = "merge_cell_test_result.xlsx"
            result_df.to_excel(test_output_path, index=False)
            print(f"\n测试结果已保存至: {os.path.abspath(test_output_path)}")
            print("\n请比较此结果文件与VBA版生成的结果文件是否一致")
            
        return results
        
    except Exception as e:
        print(f"搜索过程中发生错误: {str(e)}")
        return []

if __name__ == "__main__":
    # 首先检查合并单元格情况
    check_merge_cells()
    
    # 提示用户输入搜索文本
    search_text = input("\n请输入要测试的搜索文本 (例如 'A9BB0032098(D4569680.R1)'): ")
    if search_text.strip():
        # 执行带合并单元格支持的搜索
        search_with_merge_support(search_text.strip())
    else:
        print("未输入搜索文本，测试结束。")